﻿using ExcelHelper;
using ExcelHelper.ExcelModel;
using Microsoft.Extensions.DependencyInjection;
using SqlSugar;
using SqlSugarAndEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UpdateDatabase
{
    public class JobExcelExecuteModule:ImportCommonModule
    {
        private IBaseMethod SqlSugarProvider;
        public JobExcelExecuteModule()
        {
            IServiceCollection services = new ServiceCollection();
            services.AddSingleton<IBaseMethod, BaseMethod>();
            IServiceProvider serviceProvider = services.BuildServiceProvider();
            SqlSugarProvider = serviceProvider.GetService<IBaseMethod>();
        }
        public void DoImportJob(string FilePath,string SheetName)
        {
            ISqlSugarClient db = SqlSugarProvider.Db();
            NpoiHelper npoi = new NpoiHelper();
            List<JobModel> list = npoi.ExcelToObject<JobModel>(FilePath, SheetName);
            var Check = CheckExcelData(list,3);
            try
            {
                if (Check.Result)
                {
                    //开启事务,下面左右的操作全部用事务进行
                    db.Ado.BeginTran();
                    //第一步,插入excel里面数据库里面没有的作者
                    InsertAuthorInfo(db, list);
                    //第二部,插入excel里面数据库没有的课程
                    InsertCourseInfo(db, list);
                    //第三部，修改execl数据源，吧名称替换成为id
                    list = ExchangeJobModelInfo(db, list);
                    //第四步,插入视频信息
                    list = InsertVideo(db, list);
                    //第五步，插入视频和课程关系
                    InsertVideo_r_Course(db, list);
                    //第六步,插入课程和分类关系表
                    InsertCourse_r_catalog(db, list);
                    //第七步,插入课程和产品关系表
                    InsertCourse_r_product(db);
                    //提交事务
                    int count = GetInsertDataTotal(db,3,2);

                    if (list.Count != count)
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,数据回滚!");
                        db.Ado.RollbackTran();
                    }
                    else
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,验证成功，提交事务");
                        db.Ado.CommitTran();
                    }
                }
                else
                {
                    Console.WriteLine(Check.ErrorMsg);
                }

            }
            catch (Exception e)
            {
                //异常就回滚
                db.Ado.RollbackTran();
                Console.WriteLine(e.Message);
            }
        }
        ///// <summary>
        ///// 检查excel数据是否正确(其实就是检查excel对应关系是不是一致)
        ///// </summary>
        ///// <param name="list"></param>
        ///// <returns></returns>
        //private string CheckExcelData(List<JobModel> list)
        //{
        //    string ErrorMsg = string.Empty;
        //    //excel总数据
        //    int TotalCount = list.Count;
        //    //检查video和videopic是否为唯一对应关系
        //    int VideoCount = list.GroupBy(t => new { t.ViedoCode, t.VideoPicture }).Count();
        //    if(TotalCount!= VideoCount)
        //    {
        //        //检查videocode和pic是不是存在不唯一对应关系
        //        var ErrorVideo = list
        //            .Where(a => list.Where(b => (a.ViedoCode == b.ViedoCode && a.VideoPicture != b.VideoPicture)|| (a.ViedoCode != b.ViedoCode && a.VideoPicture == b.VideoPicture)).Any())
        //            .GroupBy(a=>new { a.ViedoCode,a.VideoPicture})
        //            .Select(a=>new { a.FirstOrDefault().ViedoCode,a.FirstOrDefault().VideoPicture});
        //        foreach(var item in ErrorVideo)
        //        {
        //            ErrorMsg += $"请检查excel，以下videocode和videopic匹配不唯一:{item.ViedoCode}:{item.VideoPicture}";
        //        } 
        //    }
        //    //检查课程和课程图片是不是唯一对应关系
        //    var ErrorCourse = list
        //        .Where(a => list.Where(b => (b.CourseName == a.CourseName && b.CoursePicture != a.CoursePicture) || (b.CourseName != a.CourseName && b.CoursePicture == a.CoursePicture)).Any())
        //        .GroupBy(a => new { a.CourseName, a.CoursePicture })
        //        .Select(a => new { a.FirstOrDefault().CourseName, a.FirstOrDefault().CoursePicture });
        //    if (ErrorCourse.Any())
        //    {
        //        foreach (var item in ErrorCourse)
        //        {
        //            ErrorMsg += $"请检查excel，以下课程名称和课程图片匹配不唯一！:{item.CourseName}:{item.CoursePicture}";
        //        }
        //    }
        //    //检查一个课程下是不是只有一个分类
        //    var GroupCataAndCourse = list
        //        .GroupBy(a => new { a.Level3, a.CoursePicture })
        //        .Select(a => new { a.FirstOrDefault().Level3, a.FirstOrDefault().CoursePicture });
        //    foreach(var course in GroupCataAndCourse)
        //    {
        //        var select = GroupCataAndCourse
        //            .Where(t => t.CoursePicture == course.CoursePicture);
        //        if (select.Count() > 1)
        //        {
        //            foreach (var item in select)
        //            {
        //                ErrorMsg += $"请检查excel，以下课程图片和分类匹配不唯一！:{item.Level3}:{item.CoursePicture}";
        //            }
        //        }
        //    }
        //    return ErrorMsg;
        //}

        /// <summary>
        /// 插入作者信息,将excel的数据和数据库数据进行比对，不存在的就插入
        /// </summary>
        /// <param name="db">sqlsugar实例</param>
        /// <param name="list">excel解析出来的数据</param>
        private  void InsertAuthorInfo(ISqlSugarClient db,List<JobModel> list)
        {
            //先获取库里面所有的作者信息
            var AuthorList = db.Queryable<authors>().ToList();
            //去重获取excel里面的所有作者信息
            var ExcelAuthors = list
                .GroupBy(t => new { t.Teacher })
                .Select(t => new authors
                {
                    Name = t.First().Teacher,
                    AuthorCode = CheckStringChinese(t.First().Teacher) ? $"{NPinyin.Pinyin.GetPinyin(t.First().Teacher) + DateTime.Now.ToString("yyyyMMdd")}".Replace(" ", "") : t.First().Teacher,
                    Infos = t.First().TeachInfo,
                    AuthorImage = t.First().TeacherPicture,
                    AddDateTime=DateTime.Now
                })
                .ToList();
            var x = db.Storageable(ExcelAuthors).WhereColumns(t => new { t.AuthorImage }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        public static bool CheckStringChinese(string text)
        {
            bool res = false;
            foreach (char t in text)
            {
                if ((int)t > 127)
                    res = true;
            }
            return res;
        }
        /// <summary>
        /// 插入课程信息
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list</param>
        private  void InsertCourseInfo(ISqlSugarClient db,List<JobModel> list)
        {
            List<courses> courses = list
                .GroupBy(t => new { t.CourseName, t.CoursePicture, t.CourseInfo })
                .Select(t => new courses
                {
                    Name = t.FirstOrDefault().CourseName,
                    CourseImage = t.FirstOrDefault().CoursePicture,
                    Infos = t.FirstOrDefault().CourseInfo,
                    CreateTime = DateTime.Now,
                    EditDateTime = DateTime.Now,
                    Status = 0,
                    KeyWords = t.FirstOrDefault().KeyWords
                }).ToList();
            var x = db.Storageable(courses).WhereColumns(t => new { t.CourseImage }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        /// <summary>
        /// 对excel解析出来的数据进行变更，把需要变更为id的字段进行替换，提升后面的效率
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">直接解析出来的数据</param>
        /// <returns>id替换之后的数据</returns>
        private  List<JobModel> ExchangeJobModelInfo(ISqlSugarClient db,List<JobModel> list)
        {
            //获取今天新插入的课程信息
            List<courses> courses = db.Queryable<courses>().Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ToList();
            var AuthorList = db.Queryable<authors>().ToList();
            var CataList = db.Queryable<catalogs>().Where(t=>t.ProductId==2).ToList();
            list.ForEach(t =>
            {
                //把课程名称变成ID
                t.CourseID = courses.Where(a => a.Name == t.CourseName).Select(a => a.Id).FirstOrDefault();
                //把讲师名称替换成ID
                t.AuthorID = AuthorList.Where(a => a.Name == t.Teacher).Select(a => a.Id).FirstOrDefault();
                //把最后一级目录名称替换成ID
                //把三级目录名称替换成ID
                var cata3 = CataList.Where(a => a.Name == t.Level3 && a.Level == 3).Select(a => a.Id.ToString()).FirstOrDefault();
                if (cata3 == null)
                {
                    //三级不存在就去找二级
                    var cata2 = CataList.Where(a => a.Name == t.Level2 && a.Level == 2).Select(a => a.Id.ToString()).FirstOrDefault();
                    //二级存在，就插入一个三级
                    if (cata2 != null)
                    {
                        catalogs newcata3 = new catalogs()
                        {
                            ProductId = 2,
                            ParentId = Convert.ToInt32(cata2),
                            Name = t.Level3,
                            Level = 3,
                            Status = 0,
                            IsTop = false,
                            CreateTime = DateTime.Now,
                            EditDateTime = DateTime.Now
                        };
                        //获取插入的实体
                        newcata3 = db.Insertable(newcata3).ExecuteReturnEntity();
                        CataList.Add(newcata3);
                    }
                    //二级不存在去找一级
                    else
                    {
                        var cata1 = CataList.Where(a => a.Name == t.Level1 && a.Level == 1).Select(a => a.Id.ToString()).FirstOrDefault();
                        if (cata1 != null)
                        {
                            catalogs newcata2 = new catalogs()
                            {
                                ProductId = 2,
                                ParentId = Convert.ToInt32(cata1),
                                Name = t.Level2,
                                Level = 2,
                                Status = 0,
                                IsTop = false,
                                CreateTime = DateTime.Now,
                                EditDateTime = DateTime.Now
                            };
                            //获得2级目录，然后再插入三级目录
                            newcata2 = db.Insertable(newcata2).ExecuteReturnEntity();
                            catalogs newcata3 = new catalogs()
                            {
                                ProductId = 2,
                                ParentId = Convert.ToInt32(newcata2.Id),
                                Name = t.Level3,
                                Level = 3,
                                Status = 0,
                                IsTop = false,
                                CreateTime = DateTime.Now,
                                EditDateTime = DateTime.Now
                            };
                            newcata3 = db.Insertable(newcata3).ExecuteReturnEntity();
                            CataList.Add(newcata2);
                            CataList.Add(newcata3);
                        }
                        //一级也不存在
                        else
                        {
                            catalogs newcata1 = new catalogs()
                            {
                                ProductId = 2,
                                ParentId = 0,
                                Name = t.Level1,
                                Level = 1,
                                Status = 0,
                                IsTop = false,
                                CreateTime = DateTime.Now,
                                EditDateTime = DateTime.Now
                            };
                            newcata1 = db.Insertable(newcata1).ExecuteReturnEntity();
                            catalogs newcata2 = new catalogs()
                            {
                                ProductId = 2,
                                ParentId = newcata1.Id,
                                Name = t.Level2,
                                Level = 2,
                                Status = 0,
                                IsTop = false,
                                CreateTime = DateTime.Now,
                                EditDateTime = DateTime.Now
                            };
                            newcata2 = db.Insertable(newcata2).ExecuteReturnEntity();
                            catalogs newcata3 = new catalogs()
                            {
                                ProductId = 2,
                                ParentId = newcata2.Id,
                                Name = t.Level3,
                                Level = 3,
                                Status = 0,
                                IsTop = false,
                                CreateTime = DateTime.Now,
                                EditDateTime = DateTime.Now
                            };
                            newcata3 = db.Insertable(newcata3).ExecuteReturnEntity();
                            CataList.Add(newcata1);
                            CataList.Add(newcata2);
                            CataList.Add(newcata3);
                        }
                    }
                }
                //获取id
                t.CatalogID = CataList.Where(a => a.Name == t.Level3  && a.Level == 3).Select(a => a.Id).FirstOrDefault();
            });
            return list;
        }

        /// <summary>
        /// 插入视频数据,并将数据源list的videoid修改
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换过id后的list</param>
        private List<JobModel> InsertVideo(ISqlSugarClient db,List<JobModel> list)
        {
            List<videos> videos = list.
                GroupBy(t => new
                {
                    t.TitleName,
                    t.ViedoCode,
                    t.VideoPicture,
                    t.KeyWords,
                    t.Duration,
                    t.Resolving,
                    t.SourceFrom,
                    t.AuthorID
                }).
                Select(t =>
                {
                    if (!double.TryParse(t.FirstOrDefault().Duration, out double value))
                    {
                        value = 0;
                    }
                    return new videos
                    {
                        VideoId = t.FirstOrDefault().ViedoId,
                        ProperTile = t.FirstOrDefault().TitleName,
                        VideoCode = t.FirstOrDefault().ViedoCode,
                        VideoImage = t.FirstOrDefault().VideoPicture,
                        VideoInfo = t.FirstOrDefault().KeyWords,
                        Duration = value,
                        ResolutionRatio = t.FirstOrDefault().Resolving,
                        ProductFrom = t.FirstOrDefault().SourceFrom,
                        AuthorId = t.FirstOrDefault().AuthorID,
                        CreateTime = DateTime.Now,
                        LastUpdateTime = DateTime.Now,
                        PublishTime = DateTime.Now,
                        KeyWords = t.FirstOrDefault().KeyWords
                    };
                }).ToList();
            var s = db.Storageable(videos).WhereColumns(t => new { t.VideoCode }).ToStorage();
            s.AsInsertable.ExecuteCommand();
            //把视频主键ID全部改为videoid
            //var sql = db.Updateable<videos>().SetColumns(t => t.Id == t.VideoId).Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ExecuteCommand();
            var videosCodes = videos.Select(t => t.VideoCode);
            videos = db.Queryable<videos>().Where(t => t.VideoCode.Contains(t.VideoCode)).ToList();
            list.ForEach(t =>
            {
                t.ViedoId = videos.Where(a => a.VideoCode == t.ViedoCode).Select(a => a.Id).FirstOrDefault();
            });
            return list;
        }
        /// <summary>
        /// 插入视频和课程之间的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list数据</param>
        private void InsertVideo_r_Course(ISqlSugarClient db,List<JobModel> list)
        {
            //获取今天新插入的课程信息
            //List<courses> courses = baseMethod.Db().Queryable<courses>().Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ToList();
            List<video_r_course> vrc = list.
                GroupBy(t => new
                {
                    t.ViedoId,
                    t.CourseID,
                }).
                Select(t => new video_r_course
                {
                    VideoId = t.FirstOrDefault().ViedoId,
                    CourseId = t.FirstOrDefault().CourseID,
                    VideoTitle = t.FirstOrDefault().TitleName,
                    CreateTime = DateTime.Now,
                    EditDateTime = DateTime.Now,
                    Status = 0,
                }).ToList();
            var s = db.Storageable(vrc).WhereColumns(t => new { t.VideoId, t.CourseId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
            //db.Insertable(vrc).ExecuteCommand();
            db.Updateable<video_r_course>().SetColumns(t => t.Order == t.Id).Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ExecuteCommand();
        }
        /// <summary>
        /// 插入课程和目录的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list数据</param>
        private  void InsertCourse_r_catalog(ISqlSugarClient db,List<JobModel> list)
        {
            List<course_r_catalog> crc = list.
                GroupBy(t => new
                {
                    t.CatalogID,
                    t.CourseID
                }).
                Select(t => new course_r_catalog
                {
                    CourseId = t.FirstOrDefault().CourseID,
                    CatalogId = t.FirstOrDefault().CatalogID,
                    EditDateTime = DateTime.Now,
                    PublishTime = DateTime.Now,
                    Year = DateTime.Now.Year.ToString(),
                    Status = 0
                }).ToList();
            var s = db.Storageable(crc).WhereColumns(t => new { t.CourseId, t.CatalogId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
        }

        /// <summary>
        /// 插入课程和产品之间的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        private  void InsertCourse_r_product(ISqlSugarClient db)
        {
            var crp = db.Queryable<courses>()
                .Where(t => SqlFunc.Subqueryable<course_r_product>().Where(a => a.CourseId == t.Id).NotAny())
                .Distinct()
                .Select(t => new course_r_product()
                {
                    CourseId = t.Id,
                    ProductId = 2,
                    Status = 0,
                    EditDateTime = SqlFunc.ToDate(DateTime.Now)
                }).ToList();
            var x = db.Storageable(crp).WhereColumns(t => new { t.CourseId, t.ProductId }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
    }
}
